package com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.repository.impl;

import com.css.fxfzxczh.constants.XczhConstants;
import com.css.fxfzxczh.modules.evaluationSummary.vo.ParamsVo;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.entity.LosRepManage;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.entity.ResHouInfo;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.queryParams.QueryParams;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.repository.LosRepEscalationRepository;
import com.css.fxfzxczh.modules.synthesizeInfoManage.earEveManage.entity.EarEveManage;
import com.css.fxfzxczh.modules.synthesizeInfoManage.knowledgeManagement.entity.KnowledgeManagement;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import com.css.fxfzxczh.web.PlatformSessionContext;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class LosRepEscalationRepositoryImpl implements LosRepEscalationRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Map<String, Object> getPage(QueryParams queParams, int curPage, int pageSize) {
        String userId = PlatformSessionContext.getUserID();
        StringBuilder sql = new StringBuilder("select b.* from  xczh_seismic_event_management a " +
                "right join xczh_loss_report_manage b on a.xsem_id=b.xsem_id ");
        StringBuilder whereSql = new StringBuilder(" where a.del_flag = '0' and b.create_user='"+userId+"'");
        StringBuilder orderSql = new StringBuilder(" order by b.create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if (!PlatformObjectUtils.isEmpty(queParams.getSign())) {
            String[] split = queParams.getSign().split(",");
            whereSql.append(" and b.check_status in ('"+String.join("','", split)+"')");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getReportType())) {
            whereSql.append(" and b.report_type = '" + queParams.getReportType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCheckStatus())) {
            whereSql.append(" and b.check_status = '" + queParams.getCheckStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getCreateUserName())) {
            whereSql.append(" and b.create_user_name like '%" + queParams.getCreateUserName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queParams.getXsemId())) {
            whereSql.append(" and b.xsem_id = '" + queParams.getXsemId() + "'");
        }

        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(EarEveManage.class)).size();
        List<LosRepManage> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(LosRepManage.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public void check(List<String> idList, String checkStatus, String checkOpinion) {
        String sql = "update xczh_loss_report_manage set check_status = ? , check_opinion = ? " +
                "where id in ('"+String.join("','", idList)+"')";
        jdbcTemplate.update(sql,checkStatus,checkOpinion);
    }

    @Override
    public void save(LosRepManage losRepManage) {
        String sql = "insert into xczh_loss_report_manage(id,xsem_id,report_type,check_status,check_opinion,create_user,create_time,update_user,update_time,create_user_name,is_hava_loss_value,attachment_id) " +
                "values(:id, :xsemId, :reportType, :checkStatus, :checkOpinion, :createUser, :createTime, :updateUser, :updateTime, :createUserName ,:isHavaLossValue, :attachmentId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(losRepManage);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public String isHaveSameTypeReport(String category,String xsemId,String userId) {
        String sql = "SELECT id FROM xczh_loss_report_manage where report_type='"+category+"' and create_user= '"+userId+"' and xsem_id = '"+xsemId+"'";
        try {
            String id = jdbcTemplate.queryForObject(sql, String.class);
            return id;
        }catch (Exception e){
            return "";
        }

    }

    @Override
    public void deleteById(String xlrmId) {
        String sql = "delete from  xczh_loss_report_manage  where id= '"+xlrmId+"'" ;
        jdbcTemplate.update(sql);
    }

    @Override
    public BigDecimal getTotalLoss(ParamsVo paramsVo) {
        StringBuilder sql = new StringBuilder("select sum(total) from (select CAST(t1.total_loss AS DECIMAL(15,2)) as total from ");
        String tableName = "";
        String reportType = paramsVo.getReportType();
        if(XczhConstants.POWER_SYS_EQUIPMENT.equals(reportType)){
            tableName = "xczh_loss_power_sys_equipment";
        }else if(XczhConstants.DRA_SYS_LOSS.equals(reportType)){
            tableName = "xczh_drainage_system_loss";
        }else if(XczhConstants.SUP_SYS_LOSS.equals(reportType)){
            tableName = "xczh_supplygas_system_loss";
        }else if(XczhConstants.HEA_SYS_LOSS.equals(reportType)){
            tableName = "xczh_heating_system_loss";
        }else if(XczhConstants.TRA_SYS_LOSS.equals(reportType)){
            tableName = "xczh_traffic_system_loss";
        }else if(XczhConstants.LON_OIL_PIP_LOSS.equals(reportType)){
            tableName = "xczh_long_oil_pipeline_loss";
        }else if(XczhConstants.COM_SYS_LOSS.equals(reportType)){
            tableName = "xczh_communication_system_loss";
        }else if(XczhConstants.RAD_TEL_SYS_LOSS.equals(reportType)){
            tableName = "xczh_radio_television_system_loss";
        }else if(XczhConstants.MUNICIPAL_FACILITIES.equals(reportType)){
            tableName = "xczh_loss_municipal_facilities";
        }else if(XczhConstants.RAILWAY_SYS_EQUIPMENT.equals(reportType)){
            tableName = "xczh_loss_railway_sys_equipment";
        }else if(XczhConstants.WATER_SYS_LOSS_EQUIPMENT.equals(reportType)){
            tableName = "xczh_loss_water_sys_equipment";
        }

        sql.append(tableName);
        StringBuilder sql2 = new StringBuilder(" t1 left join xczh_loss_report_manage t2 on t1.xlrm_id = t2.id ");
        StringBuilder whereSql = new StringBuilder(" where t2.report_type='"+reportType+"'");
        if (!PlatformObjectUtils.isEmpty(paramsVo.getXsemId())) {
            whereSql.append(" and  t2.xsem_id = '"+paramsVo.getXsemId()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(paramsVo.getProvince())) {
            whereSql.append(" and t1.province = '"+paramsVo.getProvince()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(paramsVo.getCity())) {
            whereSql.append(" and t1.city = '"+paramsVo.getCity()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(paramsVo.getCounty())) {
            whereSql.append(" and t1.county = '"+paramsVo.getCounty()+"'");
        }
        if (!PlatformObjectUtils.isEmpty(paramsVo.getEnterpriseName())) {
            whereSql.append(" and t1.enterprise_name = '"+paramsVo.getEnterpriseName()+"'");
        }
        StringBuilder groupSql = new StringBuilder(" group by t2.create_user,total_loss) t");
        try {
            BigDecimal totalLoss = jdbcTemplate.queryForObject(sql.append(sql2).append(whereSql).append(groupSql).toString(), BigDecimal.class);
            return totalLoss;
        } catch (EmptyResultDataAccessException e) {
            return BigDecimal.ZERO;
        }
    }

    @Override
    public void submit(String id) {
        String sql = "update xczh_loss_report_manage set check_status= ? where id = ?";
        jdbcTemplate.update(sql, "1", id);
    }

    @Override
    public LosRepManage findById(String id) {
        String sql = "select * from xczh_loss_report_manage where id = '"+id+"'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(LosRepManage.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
}
